之前在 day 11 ,day 16, 與 day 17 ，我們談了一些 Datalog 特有的功能，比方說：

1. 對資料表綱要 (table schema) 做查詢
2. 對交易 (transaction) 做查詢
3. Datalog 規則 (rules)
4. 跨越資料庫的查詢

那 SQL 是不是也有什麼功能是 Datalog 很難做到的呢？有，但是不多，主要是窗口函數 (window function) 的功能。

那為什麼 Datalog 查詢沒有設計窗口函數呢？這就要回到了 Datomic 的使用情境，它主要是應用於線上交易處理 (OLTP) 的情境。而窗口函數卻是在線上分析處理 (OLAP) 的情境才容易大放異彩，因為線上分析處理 (OLAP) 的情境特別需要複雜的報表生成功能。

然而，少數生成報表的使用情境在線上交易處理 (OLTP) 與線上分析處理 (OLAP) 都會遇上。儘管 Datalog 不支援窗口函數，處理一些一般的報表問題依然可以優雅地處理。

每個組別的前 n 位 (top n per group)

以 cites 這張表如下，想要用 SQL 查詢來找出「在每個國家，人口最多的兩個城市？」

我們可以用如下的查詢來達成：

SELECT
  *
FROM
  (
    SELECT
      country,
      city,
      population,
      row_number() OVER (
        PARTITION BY country
        ORDER BY
          population desc
      ) AS country_rank
    FROM
      cities
  ) ranks
WHERE
  country_rank <= 2;

此處最關鍵的技巧是使用 SQL 窗口函數裡的 row_number() 函數。

Datalog 的版本

(d/q '[:find ?country (max 2 ?population)
       :in $
       :where
       [?ct :country/name ?country]
       [?ct :city/name ?city]
       [?ct :city/population ?population]]
     (db/db))

(comment
 ;; => 
  [["France" [2181000 808000]]
   ["United Kingdom" [7825300 1016800]]
   ["United States" [8175133 3792621]]])

怎麼居然還比 SQL 窗口函數的查詢簡單多了呢？

雖然 Datalog 沒有提供處理複雜報表的窗口函數，但由於 Datalog 內建的聚合函數提供了 (max n ?variable) 這種進階的聚合函數，像『每個組別的前 n 位』這種常見的題目，反而是 Datalog 查詢的語意 (semantic) 清晰簡單許多。

然而，此處的解法還不完整，因為 (max n ?variable) 這個函數只能處理單一的變數，所以我們還看不到「城市」的資訊。

自訂聚合函數

那如果想要得到「城市」的資訊呢？

遇到這樣子相對複雜的需求，我們只好使用自訂聚合函數 (user-defined aggregate function) 來處理了。

在下方的例子裡：

• max-by-population 是自訂聚合函數。在 day13 時，我們有討論過執行環境的議題：自訂聚合函數並不需要特別的安裝，這是因為 Datomic 的特殊架構。
• 在 Datalog 查詢裡呼叫它時，需要提供完整的 namespace，所以會變成 repl-sessions.day18/max-by-population
• tuple 是 Datomic 的內建函數，可以用來把 ?city 與 ?population 變數一起打包成為「數組」
• 程式碼連結

(defn max-by-population
  [coll]
  (let [result (sort-by second > coll)]
    (take 2 result)))

(d/q '[:find ?country (repl-sessions.day18/max-by-population ?tup)
       :in $
       :where
       [?ct :country/name ?country]
       [?ct :city/name ?city]
       [?ct :city/population ?population]
       [(tuple ?city ?population) ?tup]]
     (db/db))

(comment
  ;; => 
  [["France" (["Paris" 2181000] ["Marseille" 808000])]
   ["United Kingdom" (["London" 7825300] ["Birmingham" 1016800])]
   ["United States" (["New York" 8175133] ["Los Angeles" 3792621])]])

其它資源

1. 歡迎訂閱 PruningSuccess 電子報，主要談論軟體開發、資料處理、資料分析等議題。
2. 歡迎加入 Clojure 社群